PostgreSQL Don't Do This 中文译本

李守中是开源软件理念坚定的支持者，所以译本虽不是软件，但依旧仿照开源软件的协议发布:

• 无担保：本文作者不保证作品内容准确无误，亦不承担任何由于使用此文档所导致的损失。
• 自由使用：任何人都可以自由地 阅读/链接/打印 此文档，无需任何附加条件。
• 名誉权：任何人都可以自由地 转载/引用/再创作 此文档，但必须保留作者署名并注明出处。

如果读者发现作品中有错误的地方，劳请来信指出。任何提高作品质量的建议李守中都将虚心接纳。

本文档根据 PostgreSQL Don't Do This 翻译。

对于直译后无法准确描述软件行为的句子，李守中会根据软件行为对这些句子进行意译。

译者注 的部分是李守中对于该内容的补充说明。

受限于李守中的中文水平，在对句子进行意译时可能会偏离作者的本意，请读者谨慎参考。

有能力的读者可以从此链接 PostgreSQL Don't Do This.txt 下载英文原文与本文做对照。

为什么？

使用 SQL_ASCII 编码意味着数据库不做编码转换。也就是说，要被存储的二进制数据被简单地视为已经处于新编码中，数据库会对这些二进制数据做针对 SQL_ASCII 编码的有效性检查，而不考虑这些二进制数据的含义。除非特别小心，一个使用 SQL_ASCII 编码的数据库最终通常存储了混合着不同编码的内容，无法可靠地恢复原始字符。

译者注: 如果数据库使用 UTF8 编码，客户端使用 GBK 编码，那么客户端的数据发到数据库之后，数据库会把 GBK 编码的数据转成 UTF8 再存。

译者注: 对于存数据来说，如果数据库使用了 SQL_ASCII 编码，那么对于客户端传过来的数据，数据库做针对 SQL_ASCII 编码的有效性检查，不做编码转换。(a) 如果客户端不使用 SQL_ASCII 编码，数据有可能存不进去 (过不了有效性检查)，比如，如果客户端传过来的数据中使用了 SQL_ASCII 指定的转义字符的话就存不进去。(b) 如果客户端也使用了 SQL_ASCII 编码，数据库不做针对 SQL_ASCII 编码的有效性检查，不做编码转换，直接存。

译者注: 对于读数据来说，如果数据库使用了 SQL_ASCII 编码，那么对于检索出来的数据，数据库直接发给客户端。(a) 如果客户端不使用 SQL_ASCII 编码，那么客户端在收到数据之后需要自己转换编码才能正常显示。(b) 如果客户端使用 SQL_ASCII 编码，那么客户端在收到数据之后不做编码转换，直接显示。

什么时候能用？

如果你的输入数据已经是一个无望的未标记编码的混合，比如 IRC 频道日志或不符合 MIME 标准的电子邮件，那么可以将 SQL_ASCII 作为最后的手段。但在这之前，最好先考虑能不能使用 bytea 来行来存储数据，或者是否可以自动检测 UTF8 并假设非 UTF8 数据位于某些特定编码 (例如 WIN1252) 中。

译者注: 当要存的数据已经混合了多种编码，难以 (无法) 解析，那么这时候可以先考虑使用 bytea 类型，这个类型直接存储二进制值而不涉及编码转换，可以避免编码问题，并且可以将数据原始的二进制值存储在数据库中。

译者注: 如果可以判断要存的数据由哪些编码组成，但又不想发生额外的编码转换问题，也可以使用 bytea 来存储这种数据。

译者注: 比如，一段数据混合了 UTF8 和 WIN1252 编码，如果数据库将这段数据按照 UTF8 编码来转换到其他编码，那么数据中使用 WIN1252 编码的部分也会被按照 UTF8 编码来处理。而通常客户端有能力处理这段使用多种编码方式的数据，数据库只需要存储即可，不需要做编码转换这种多余的操作。

这个行为可以节省一次与数据库的通信，但也仅限于此。

为什么？

使用 --password 或 -W 会告诉 psql 在尝试连接服务器之前提示用户输入密码，但即使服务器不需要密码，也会出现输入密码的提示。

如果用户登入数据库时需要密码，那么 psql 在与数据库通信之后会自动出现输入密码的提示。如果用户认为登入数据库时需要密码，而实际上不需要，那么用户的这个错误认识可能会导致一些问题。

比如，如果用户使用 -W 连接到一个配置为允许通过 peer 授权访问的数据库，用户可能会认为数据库要求客户端提供密码，但实际上并不需要。此时，如果被登入的数据库用户没有设置密码，或者用户输入了错误的密码，用户仍然可以成功登入。但是，此时用户可能认为自己输入了正确的密码。

然而，当用户尝试从其他客户端 (例如通过本地主机连接) 或者以其他身份 (主机上的其他用户) 登入数据库时，这个错误的密码可能会导致登录失败。这是因为相应数据库用户的密码和用户输入的密码可能不同，因此用户输入的密码无法通过认证，也就无法登入。

什么时候能用？

别用。

用户应使用 trigger 作为 rule 的替代。

为什么？

rule 的功能非常强大，但它们的功能并不像它们看起来的那样。rule 看起来像是一些条件逻辑，但实际上它们会重写查询或添加额外的查询。

这意味着所有 non-trivial rule 都不正确。 译者注: 我不知道这个不正确是指什么。

Depesz 在这方面有 更多的见解 。

什么时候能用？

别用。

用外键 (foreign key) 来代替表继承。

为什么？

表继承是当时面向对象潮流的产物，它让数据库与面向对象的代码紧密耦合。事实证明，将事物紧密耦合并不能真正产生期望的结果。

什么时候能用？

几乎别用。现在，由于表分区可以进行本地化处理，表继承的常见用途已被本地特性取代，该特性可以处理元组路由等，而无需定制的代码。

极少数的例外之一是需要在特殊情况下使用 temporal_tables 扩展，如果想要在缺乏 SQL 2011 支持的情况下紧急使用它来做行版本控制。表继承将提供一个小的快捷方式，而不是使用 UNION ALL 来获取历史和当前行。即便如此，在操作父表时仍应注意一些事情。

不要使用 NOT IN 或者任何 NOT 和 IN 的组合，比如 NOT (x IN (SELECT ...))

为什么？

两个理由。

第一，如果出现 null 则 NOT IN 会以不被期待的方式运行:

select * from foo where col not in (1, null); -- 总是返回 0 行
select * from foo where foo.col not in (select bar.x from bar); -- 如果 bar.x 出现 null 那么返回 0 行

在 SQL 中，null 是个占位符，不表示任何值，所以 null 不可运算。即，任何值或 null 与 null 运算结果都为 null (既不是 true 也不是 false)。比如:

col IN (1, null)        -- 返回值以 col=1 为准
=>
col = 1 OR col = null

col NOT IN (1, null)    -- 返回值总是 null
=>
col != 1 AND col != null

OR 和 AND 的右侧都涉及 null 运算，这意味着 OR 和 AND 的右侧都是 null。所以在第二个例子中，null 和任何值做 AND 计算结果都为 null，所以 col NOT IN (1, null) 的值总是 null。

第二，第一点导致 NOT IN (SELECT ...) 无法被很好地优化。特别是，查询规划器无法将其转换为 anti-join，因此它会变成 hashed Subplan 或 plain Subplan。hashed Subplan 速度快，但查询规划器只允许在结果集较小的情况下使用该计划；plain Subplan 的速度非常慢 (O(N^2))。这意味着性能在小规模测试中可能看起来很好，但一旦越过某个大小阈值，性能可能会减慢 5 个或更多数量级，用户肯定不希望发生这种情况。

替代方案是使用 NOT EXISTS (SELECT ...):

select * from foo where not exists (select from bar where foo.col = bar.x);

什么时候能用？

NOT IN (list,of,values,...) 在 () 内部没有空值时可以安全使用。要从查询结果中排除特定的常量值时，可以使用 NOT IN。

为什么？

PG 会将所有不被双引号引用的表名，列名以及其他任何名称都转成小写。

所以 create table Foo() 最终创建的表名为 foo 但 create table "Bar"() 创建的表名是 Bar 。

这些查询语句可以正常使用: select * from Foo, select * from foo, select * from "Bar" 。

这些查询语句会报错 "no such table": select * from "Foo", select * from Bar, select * from bar 。

这意味着，如果用户在表名或者列名中使用大写字母，那么在查询时，必须为这些表名或列命添加双引号。这很烦人。如果用户使用其他工具连接数据库，那么怎么正确地加上双引号，也很麻烦。

固定使用 a-z, 0-9 以及下划线的组合作为名称就永远不需要操心这些问题了。

什么时候能用？

如果让名称中的字母以大写形式展现在输出中，或者其他地方，那么这时候就可以使用这个功能。但也可以让表名保持小写，使用 column aliase 来让输出的列名显示大写的字母以达到在输出中显示大写字母的效果: select character_name as "Character Name" 。

为什么？

BETWEEN 指定了一个闭区间: 两端的值也被包含在比较范围中。

SELECT * FROM <table> WHERE <timestamp-col> BETWEEN '2018-06-01' AND '2018-06-08'

我们通常想要比较的是 ['2018-06-01 00:00:00.000000', '2018-06-08 00:00:00.000000') 这个左闭右开的区间内的时间。

或者不严谨地说是 ['2018-06-01 00:00:00.000000', '2018-06-07 23:59:59.999999'] 这个区间的时间。

而上面那条 SQL 比较的是 ['2018-06-01 00:00:00.000000', '2018-06-08 00:00:00.000000'] 这个闭区间内的时间。

区别是，2018-06-08 00:00:00.000000 这个本不该进入比较范围的时间点也被包含进比较范围了。

如果数据库刚好有午夜 (2018-06-08 00:00:00.000000) 的记录，那么这条记录可能会被重复统计。

这条 SQL 应该写成这样:

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

什么时候能用？

BETWEEN 在比较 int 或 date 数据类型的时候很好用，只要记住范围两端的值也进入比较范围即可。但习惯这一点会养成不好的习惯。

不要使用 timestamp 来存储时间戳，要使用 timestamptz (timestamp with time zone) 来存储时间戳 为什么？

timestamptz 存储的是 UTC 时区的时间戳 (自 2000-01-01 以来的微秒数)，所以它存储的是某个特定的时间点。默认情况下，它会把时间转换到客户端所在时区来显示，但用户可以使用 at time zone 将时间转换成其他时区的表示方式。

因为 timestamptz 存储的是某个特定的时间点，所以用户可以用它来做算术运算。

timestamp (timestamp with out time zone) 并不能做到这一点，它只存储用户给它的日期和时间，而不记录这个时间是哪个时区的时间。这意味着这个时间并不能精确地表示某个时间点，也就无法参与计算。

因此，如果要存储一个确定的时间点，使用 timestamptz。

什么时候能用？

如果以抽象的方式处理时间戳，或者只是从应用程序中保存和检索它们，而不需要对它们进行运算，那么时间戳可能是合适的。

译者注: 如果非常关注未来的本地时间，而且用户所处时区有可能经常变化，比如日历应用的提醒功能: 需要在本地时区的 7:00 am 推送消息。那么这时候不带时间戳的 timestamp 更好用。

通常，从其他对时区支持有限的数据库迁移到 PG 上才会出现这种用法。

请将时间戳与时区一起使用。

译者注: 也可以这样将存储了 UTC 时间的 timestamp 转换为 timestamptz:

-- 假设要被转换的 timestamp 列存了 UTC 时间
alter table <table-name>
    alter <col-name> type timestamptz
        using <col-name> at time zone 'UTC';

为什么？

因为数据库不知道 UTC 是这个存储了 timestamp 的列的默认时区

这使许多原本有用的时间计算变得复杂。例如，显示 u.timezone 给出的时区中的最后一个午夜:

date trunc('day', now() at time zone u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC';

u.timezone 中 x.datecol 日期之前的午夜:

date trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone) AT TIME ZONE u.timeone AT TIME ZONE 'UTC';

什么时候能用？

如果优先考虑对时区支持有限的数据库的兼容的话，可以用。

不要使用 timetz 类型，用 timestamptz 替代。

为什么？

提供这个类型只是为了符合 SQL 标准。

带时区信息的 time 类型是由 SQL 标准定义的，但是这个类型的有用性受到质疑。在大多数情况下，date, time, timestamp 和 timestamptz 的组合可以满足任何程序对于时间和日期的需求。

什么时候能用？

别用。

用以下函数来代替 CURRENT_TIME:

• CURRENT_TIMESTAMP 或 now() 可以提供 timestamptz 类型的时间戳
• LOCALTIMESTAMP 可以提供 timestamp 类型的时间戳
• CURRENT_DATE 可以提供 date 类型的日期
• LOCALTIME 可以提供 time 类型的时间

为什么？

CURRENT_TIME 返回的类型是 timetz，见上一节。

什么时候能用？

别用。

对于时间戳的强制转换，不要指定精度，尤其不要使用 0。改用 date_trunc ('second', ...) 等。

译者注: timestamp 和 timestamptz 有微秒级的精度，即，秒后六位小数。括号内的数字可以控制保留几位精度，最大精度微秒对应 6，也是默认；最小精度秒对应 0。

为什么？

因为对需要保留的部分采用四舍五入，而不是像大家所期望的那样直接截断。这可能会导致意外问题。

什么时候能用？

别用

用 text 类型来代替 char(n)。

为什么？

如果用户输入的字符长度不足 n 就会用空格补足 n 位。用户可能不想这样。

PG 的手册说:

char 类型的值在物理上会被空格填充到指定的宽度 n，并以这种方式存储和显示。然而，当比较两个类型字符的值时，尾随空格被视为语义上不重要的，并被忽略。在空白显著的排序规则中，这种行为可能会产生意外的结果；例如 SELECT 'a'::CHAR(2) collate "C" < E'a\n'::CHAR(2); 返回 True，即使 POSIX (C) 中空格大于换行符。将字符值转换为其他字符串类型时，会删除尾部空格。请注意，当使用模式匹配时 (LIKE 和正则表达式)，尾部的空格在字符变化和文本值中具有语义意义。

译者注: 上面这段是在说，在字符串比较时，尾部空格会被忽略；在使用模式匹配时，尾部空格不被忽略。上面那句 SELECT 中的 E 表示后面的字符串包含了被转义的字符，即 \n。这个 \n 在保存时会被按换行符处理。

这可能会把你吓跑。

用空格填充会浪费空间，但不会让与它相关的操作变得更快；事实上正相反，由于许多操作需要去除字符串前后的空格，这些多余的空格会让程序更慢。

需要注意的是，从存储的角度来看，char(n) 不是一个固定宽度的类型。由于有些字符可能需要占用多个字节 (比如中文)，因此即使有空格补位 char(n) 的长度也无法固定。

译者注: 如果 char(n) 中只存储 ASCII 字符的话，在空格补位之后，每条记录的长度可以确定为 n。

什么时候能用？

在使用使用了固定宽度字段的非常非常旧的软件时，或者，当你阅读上面 PG 文档中的片段时，你会想 「这很有道理，很符合我的要求」，而不是胡言乱语然后逃跑。

有时，人们对「不要使用char(n)」的回应是「我需要值始终为n个字符长」(例如，国家代码、哈希或其他系统的标识符)。即使在这种情况下，使用 char(n) 仍然是个坏主意。

使用带 CHECK (length(VALUE)=3) 或 CHECK (VALUE ~'^[[:alpha:]]{3}$') 或类似内容的，text 类型或 domain over text。

译者注: domain 在本质上是一个带有可选约束的数据类型 (限制允许的取值范围)。定义域的用户成为其所有者。domain over text 是指自建一个基于 text 的数据类型并加上约束。

为什么？

因为 char(n) 不会拒绝过短的值，数据库只会用空格补足长度。因此，这种使用方式与使用带有检查确切长度的约束的 text 类型相比并没有实际好处。带有长度检查约束的 text 类型还可以验证值的格式是否正确。

请记住，与 varchar(n) 相比，使用 char(n) 没有任何性能优势。事实恰恰相反。出现的一个特殊问题是，如果将 char(n) 类型的值与明确指定为 text 或 varchar 类型的值比较，则在比较过程中可能无法使用索引。这可能很难调试，因为它不会显示在手动查询中。

什么时候能用？

别用。

用 varchar 或者 text 类型来替代 varchar(n)。

为什么？

varchar(n) 类型的字段是变长的文本字段，插入一条字符数大于 n 的数据会报错。

varchar (不带 (n) 限制) 与 text 两个类型很像，都不带长度限制。如果在三种字段类型中插入相同的字符串，它们将占用完全相同的空间，并且无法测量性能上的任何差异。

译者注: varchar 和 text 两个类型在存储层面都用了 varlena (变长数组) 类型，所以在使用中可以把它们看作相同类型。

如果真的需要一个有长度限制的 text 字段，那就用 varchar(n)。但如果用 varchar(20) 作为姓氏字段，那么 Hubert Blaine Wolfe­schlegel­stein­hausen­berger­dorff 这个人就无法注册服务。

有些数据库没有能容纳任意长度文本的数据类型，或者如果有，就没有 varchar(n) 那么方便、高效或受支持。当那些数据库的用户想要 PG 中的 text 类型时，他们通常会在那些数据库中使用类似于 varchar(255) 的类型。

译者注: 使用 255 这个数字是因为当 varchar 定义的长度大于 255 时，每条数据就需要用 2 个字节来存储文本的实际长度。

如果需要约束字段中的值，则可能需要比限制最大长度更具体的东西: 可能还有最小长度，或者将值限制在一组有限的字符中。check constraint 可以做到这些事情，也可以限制最大字符串长度。

什么时候能用？

如果想要的是一个文本字段，插入超过长度限制的字符串会报错，并且不想使用显式的 check constraint，那么 varchar(n) 是一个非常好的类型。使用前思考一下是否符合条件。

另外，varchar 类型在 SQL 标准中，而 text 类型不在。因此 varchar 可能是编写具有强可移植性的应用程序的最佳选择。

实际上 money 类型不是用来存储货币值的最好选择。numeric 或 integer (很少见) 类型可能更好。

为什么？

好多原因 。

money 类型是一个定点 (fixed-point) 小数类型，使用 machine int 实现，运算起来很快。但它不能处理货币的小数部分 (比如美分或其他货币的等价单位)，它四舍五入的行为可能不是你想要的。

译者注: 上面那段说的是，money 类型只能处理小数点后两位，从第三位开始四舍五入。比如 select '1.336'::money; 会返回 1.34。

money 类型不存储货币的单位，而是假设所有类型为 money 的列都包含由数据库的 lc_montary 区域设置指定的货币。如果修改 lc_montary 的值，则所有类型为 money 列都将包含错误的值。这意味着，如果在 lc_montary 设置为 'en_US.UTF-8' 时插入 '$10.00'，那么在修改 lc_montary 的值后，检索到的值可能为 '10,00 Lei' 或 '¥1000'。

译者注: 上面那段在最后说的有问题，如果数据库里存的是美元 '$10.00' 那么货币类型修改之后，比如改成人民币，那么从数据库读出来的值还是 10.00 只是单位变而已。

译者注: 上面那段说，money 类型不存储货币的单位，如果 lc_montary='en_US.utf8' (代表美元)，那么 select '500'::money; 会输出 $500 。修改 lc_montary='en_HK.utf8' (港元)，那么 select '500'::money; 会输出 HK$500 。

译者注: HKD 和 HK$ 均代表港元。HKD 是港元英文名称 Hong Kong Dollar 的缩写，HK$ 是港元的标志。两者后面的 D 与 $ 没有特别的含义，只是一种特定的使用符号，用来区别港元名称和港元标志。

可以将数值与货币单位一起存储在相邻的列中，以避免 money 类型的问题。这种做法可以提供更大的灵活性，可以更好地控制货币值的精度、单位和计算。

什么时候能用？

如果只用一种货币类型，不处理小于美分 (只处理小数点后两位) 的精度，并且只对 money 类型的值做加减操作，那么可以使用 money 类型。

对于新应用来说，应该使用 identity 列来替代 serial 类型。 译者注: 详见 PostgreSQL 中的自增列 。

为什么？

serial 类型在模式 (schema)，依赖关系 (dependency) 和权限管理方面具有一些 奇怪的行为，这些行为使得这些方面的操作变得繁琐且复杂。

什么时候能用？

• 使用 PG 10 及之前的版本 译者注: identiry 列是 PG 11 引入的
• 在特定的表继承组合中
• 以某种方式对多个表使用相同的序列。尽管在这些情况下 explicit declaration 可能比 serial 类型更合适。

不要在任何生产环境中使用基于 TCP/IP (host, hostssl) 的 trust 授权 (trust authentication)。

译者注: 就是不要在配置文件中直接写允许某个 IP 登录，因为 IP 可能会变，这会给数据库带来安全隐患。

特别是不要在 pg_hba.conf 文件中设置这样的行: host all all 0.0.0.0/0 trust 。这允许许互联网上的任何人作为集群中的任何 PostgreSQL 用户进行身份验证，包括 PostgreSQL 超级用户。

有一个 身份验证方法的列表，可以从表中选择适合的方法来建立到 PostgreSQL 的远程连接。设置基于密码的身份验证方法相当容易，建议使用在 PostgreSQL 10 及以上版本中可用的 scramb-sha-256 方法。

为什么？

PG 的文档说:

trust authentication 只适用于用户信任 pg_hba.conf 中指定的允许连接到服务器的每台计算机上的每一个用户的情况。除了来自 localhost (127.0.0.1) 的 TCP/IP 连接之外，任何基于 TCP/IP 的 trust 授权的连接都应被认为不可信。

使用基于 TCP/IP 的 trust 授权时，任何用户都可以声称自己是其他用户，PG 完全信任用户声称的身份。这意味着有人可以声称自己是 postgres 超级用户帐户，PG 将接受这一说法并允许他们登录。

更进一步，在生产环境中，在 UNIX 的 local socket 上使用 trust 授权也不是一个好主意，因为任何有权访问正在运行的 PG 实例的人都可以作为任何用户登录。

什么时候能用？

别用。

说的多一些就是，下面这些情况可以使用基于 TCP/IP 的 trust 授权:

• 在受信任的网络上运行 CI/CD 作业中的 PostgreSQL
• 在本地开发机器上工作，但只在基于 TCP/IP 的 trust 授权中配置 localhost

但也应该看看是否有其他更合适的方法。例如，在基于 UNIX 的系统上，可以使用 peer 授权 (peer authentication) 连接到本地开发环境。